﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Database;

namespace ConnectDatabase
{
    public class GeoAirportDB
    {
        DatabaseDataContext dbContext = new DatabaseDataContext();

        public struct RawData
        {
            public string AirportCode { get; set; }
            public string AirportName { get; set; }
            public string CityCode { get; set; }
            public string CityName { get; set; }
            public string CountryName { get; set; }
        }

        #region Các hàm cơ bản
        public List<tblGeoAirport> GetAll()
        {
            try
            {
                var list = from a in dbContext.tblGeoAirports where a.Visible select a;
                return list.ToList<tblGeoAirport>();
            }
            catch
            {
                return new List<tblGeoAirport>();
            }
        }

        public tblGeoAirport GetByKey(string code)
        {
            try
            {
                var obj = dbContext.tblGeoAirports.SingleOrDefault(a => a.Code == code && a.Visible);
                if (obj == null)
                {
                    obj = new tblGeoAirport();
                    obj.Code = code;
                    obj.Name_Vi = code;
                    obj.Name_En = code;
                    obj.Name_Fr = code;
                }
                return obj;
            }
            catch
            {
                return null;
            }
        }

        public bool Insert(tblGeoAirport obj)
        {
            bool isOk = false;
            try
            {
                var exist = dbContext.tblGeoAirports.SingleOrDefault(a => a.Code == obj.Code && !a.Visible);
                if (exist != null)
                {
                    exist.CityCode = obj.CityCode;
                    exist.Name_Vi = obj.Name_Vi;
                    exist.Name_En = obj.Name_En;
                    exist.Name_Fr = obj.Name_Fr;
                    exist.Location = obj.Location;
                    exist.Visible = obj.Visible;
                    dbContext.SubmitChanges();
                    isOk = true;
                }
                else
                {
                    dbContext.tblGeoAirports.InsertOnSubmit(obj);
                    dbContext.SubmitChanges();
                    isOk = true;
                }
            }
            catch
            {
                return false;
            }

            return isOk;
        }

        public bool Update(tblGeoAirport obj)
        {
            bool isOk = false;
            try
            {
                var exist = dbContext.tblGeoAirports.SingleOrDefault(a => a.Code == obj.Code);
                if (exist != null)
                {
                    exist.Name_Vi = obj.Name_Vi;
                    exist.Name_En = obj.Name_En;
                    exist.Name_Fr = obj.Name_Fr;
                    dbContext.SubmitChanges();
                    isOk = true;
                }
            }
            catch
            {
                isOk = false;
            }
            return isOk;
        }

        public bool Delete(string code)
        {
            bool isOk = false;
            try
            {
                var exist = dbContext.tblGeoAirports.SingleOrDefault(a => a.Code == code);
                if (exist != null)
                {
                    exist.Visible = false;
                    dbContext.SubmitChanges();
                    isOk = true;
                }
            }
            catch
            {
                isOk = false;
            }
            return isOk;
        }
        #endregion

        public List<tblGeoAirport> GetByCountryCode(string countryCode)
        {
            try
            {
                if (string.IsNullOrEmpty(countryCode))
                {
                    var list = from a in dbContext.tblGeoAirports where a.Visible select a;
                    return list.ToList<tblGeoAirport>();
                }
                else
                {
                    var list = from a in dbContext.tblGeoAirports where a.tblGeoCity.tblGeoCountry.Code == countryCode && a.Visible select a;
                    return list.ToList<tblGeoAirport>();
                }
            }
            catch
            {
                return new List<tblGeoAirport>();
            }
        }

        public List<RawData> SearchByKeyword(string keyword)
        {
            try
            {
                //var list = from a in dbContext.tblGeoAirports
                //           where a.Visible &&
                //               (a.Code.Contains(keyword)
                //               || a.CityCode.Contains(keyword)
                //               || a.Name_Vi.Contains(keyword)
                //               || a.Name_En.Contains(keyword)
                //               || a.Name_Fr.Contains(keyword)
                //               )
                //           select a;
                //return list.ToList<tblGeoAirport>();



                var list = (from a in dbContext.tblGeoAirports
                            where a.Code.Contains(keyword) || a.CityCode.Contains(keyword)
                            orderby a.Code descending
                            select new RawData
                            {
                                AirportCode = a.Code,
                                AirportName = a.Name_Vi,
                                CityCode = a.CityCode,
                                CityName = a.tblGeoCity.Name_Vi,
                                CountryName = a.tblGeoCity.tblGeoCountry.Name_Vi
                            }).Take(25).ToList<RawData>();

                if (list.Count == 0)
                {
                    var list1 = (from a in dbContext.tblGeoAirports
                                 where (
                                     a.Name_Vi.Contains(keyword) ||
                                     a.Name_En.Contains(keyword) ||
                                     a.Name_Fr.Contains(keyword) && (!a.Code.Contains(keyword) && !a.CityCode.Contains(keyword)))
                                 orderby a.Code descending
                                 select new RawData
                                 {
                                     AirportCode = a.Code,
                                     AirportName = a.Name_Vi,
                                     CityCode = a.CityCode,
                                     CityName = a.tblGeoCity.Name_Vi,
                                     CountryName = a.tblGeoCity.tblGeoCountry.Name_Vi
                                 }).Take(25).ToList<RawData>();

                    foreach (RawData item in list1)
                    {
                        list.Add(item);
                    }

                    if (list.Count == 0)
                    {
                        var list2 = (from a in dbContext.tblGeoAirports
                                     where (
                                         a.tblGeoCity.Name_Vi.Contains(keyword) ||
                                         a.tblGeoCity.Name_En.Contains(keyword) ||
                                         a.tblGeoCity.Name_Fr.Contains(keyword) && (!a.Code.Contains(keyword) && !a.CityCode.Contains(keyword)))
                                     orderby a.Code descending
                                     select new RawData
                                     {
                                         AirportCode = a.Code,
                                         AirportName = a.Name_Vi,
                                         CityCode = a.CityCode,
                                         CityName = a.tblGeoCity.Name_Vi,
                                         CountryName = a.tblGeoCity.tblGeoCountry.Name_Vi
                                     }).Take(25).ToList<RawData>();
                        
                        foreach (RawData item in list2)
                        {
                            list.Add(item);
                        }
                    }
                }

                return list;


            }
            catch
            {
                return new List<RawData>();
            }
        }
    }
}
